from tkinter import *
from tkinter import filedialog
from tkinter import ttk
from threading import Thread
from queue import Queue
import openpyxl.workbook
from config import *
from sql import sqlseverDB
from mini_tools.cjjtool import *
from mini_tools.myexcel import *
from mini_tools.mytools import *
import tkinter.messagebox as messagebox
import openpyxl,time,concurrent.futures,json,os

class DesFYFra(Frame):
    def __init__(self,master=None):
        Frame.__init__(self,master)
        DesfyFrame(self).pack(fill=X,padx=2,pady=2)

class DesfyFrame(Frame):

    def __init__(self,master=None):
        Frame.__init__(self, master)
        self.ms = sqlseverDB('{SQL Server}', SQL_SERVER, 'SuMaiTongPol', SQL_USER, SQL_PWD)
        self.statustxt2=StringVar()
        self.statustxt2.set(f'未导入已翻译表')
        self.createWidgets()

    def createWidgets(self):

        Label(self, text='描述翻译', fg="white", bg="#007FFF",font=("微软雅黑", 13),width=50).pack(fill=X,padx=5)

        self.statustxt=StringVar()
        self.statustxt.set('正在获取数据...')
        f0=Frame(self)
        f0.pack(fill=X,padx=2,pady=2,side=LEFT)
        f03=Frame(f0)
        f03.pack(pady=2,fill=X,padx=2)
        Label(f03, textvariable=self.statustxt,fg='red').pack(side=LEFT, padx=2, pady=2)
        Thread(target=self.getkwcount).start()

        f00=Frame(f0)
        f00.pack(pady=2,fill=X,padx=2)

        self.btn_dc=Button(f00,text='导出原始描述',fg='#ffffff',bg='green',command=lambda:Thread(target=self.dc_des).start())
        self.btn_dc.pack(side=LEFT, padx=2, pady=2)

        f01=Frame(f0)
        f01.pack(pady=5,fill=X,padx=5)
        Label(f01, text='导入翻译表:').pack(side=LEFT, padx=2, pady=2)
        self.DaoRuIpt = Entry(f01)
        self.DaoRuIpt.pack(side=LEFT, padx=2, pady=2)
        Button(f01,text='选择文件',fg='#ffffff',bg='green',command=self.choosefile).pack(side=LEFT,padx=2, pady=2)
        
        f02=Frame(f0)
        f02.pack(fill=X,pady=2,padx=2)
        self.btn_dr=Button(f02,text='上传印尼描述',fg='#ffffff',bg='green',command=lambda:Thread(target=self.dr_yndes).start())
        self.btn_dr.pack(side=LEFT, padx=2, pady=2)
        Label(f02, textvariable=self.statustxt2,fg='red').pack(side=LEFT, padx=2, pady=2)
    
    def getkwcount(self):
        while True:
            try:
                
                rs=self.ms.ExecQuery('''SELECT COUNT(DISTINCT itemid) from ProShopeePh''')
                
                rs1=self.ms.ExecQuery('''SELECT COUNT(DISTINCT itemid) from ProShopeePh where yndes is not null''')
                
                self.statustxt.set(f'需翻译描述共 {rs[0][0]} 个,其中已翻译 {rs1[0][0]} 个,未翻译 {rs[0][0]-rs1[0][0]} 个')
                time.sleep(10)
            except Exception as e:
                continue

    def dc_des(self):
        
        if not messagebox.askyesno('提示','是否导出未翻译描述?'):
            return
        
        self.btn_dc.config(state=DISABLED)
        print('正在链接数据库...')
        rp=self.ms.ExecQuery(f'''select ynitemid1,ynurl1,pdec from ProShopeePh where [id] in (select MIN(id) from ProShopeePh where
                                newmaxprice is not null and yndes is null GROUP BY ynitemid1) order by [id]''')

        
        wb=openpyxl.Workbook()
        ws=wb[wb.sheetnames[0]]
        ws.append(['印尼ItemID','印尼虾皮一次铺货链接','产品描述','印尼描述'])
        row=0
        for ynitemid,ynurl1,pdes in rp:

            try:
                ws.append([ynitemid,ynurl1,pdes,''])
                row+=1
            except Exception as e:

                print(f'第 {row} 行,导出错误 => {e}')

        current_time_str = time.strftime("%Y-%m-%d_%H-%M-%S", time.localtime(time.time()))
        cur_fname=f'原始描述_{current_time_str}.xlsx'
        wb.save(cur_fname)
        print(f'本次导出描述完毕,共导出{row}行')
        messagebox.showinfo('提示',f'本次导出描述完毕,共导出{row}行')
        self.btn_dc.config(state=NORMAL)

    def choosefile(self):
        select_path=filedialog.askopenfilename()
        if select_path.endswith('.xlsx'):
            self.DaoRuIpt.delete(0, END)
            self.DaoRuIpt.insert(0,select_path)
            self.fpath=select_path
            wb=openpyxl.load_workbook(select_path)
            ws=wb[wb.sheetnames[0]]
            max_row= ws.max_row
            self.statustxt2.set(f'当前翻译表共{max_row-1}行')
        else:
            messagebox.showerror('错误提示','请选择excel文件')
    
    def dr_yndes(self):

        if not self.DaoRuIpt.get() :
            messagebox.showinfo('提示','请先导入翻译表')
            return
        self.btn_dr.config(state=DISABLED)
        wb=openpyxl.load_workbook(self.fpath)
        ws=wb[wb.sheetnames[0]]
        tsb=getDictDatasByEecel(ws,0)
        ws.delete_rows(2,ws.max_row-1)
        self.ws=ws
        self.cpcount=0
        with concurrent.futures.ThreadPoolExecutor(max_workers=10) as executor:
            # 提交任务给线程池
            cr=2
            for item in tsb:
                if item['印尼描述']:
                    executor.submit(self.uptask, [item['印尼ItemID'],item['印尼描述'],item['产品描述'],cr])
                else:
                    self.ws.append(list(item.values()))
                cr+=1
                

        executor.shutdown(wait=True)

        print(f'全部上传完毕,共上传 {self.cpcount} 行')
        messagebox.showinfo('提示',f'全部上传完毕,共上传 {self.cpcount} 行')
        wb.save(self.fpath)
        self.btn_dr.config(state=NORMAL)

    def uptask(self,item):
        yn_itemid,yndes,pdes,cr=item
        try:
            aff=self.ms.ExecNoQuery('update ProShopeePh set yndes=? where ynitemid1=?',(yndes,yn_itemid))
            
            if aff>0:
                self.cpcount+=1
                print(f'第{cr}行,yn_itemid:{yn_itemid},上传成功,当前共上传: {self.cpcount} 行')
            else:
                print(f'第{cr}行,yn_itemid:{yn_itemid},上传失败')
                self.ws.append([yn_itemid,'','',yndes])

        except Exception as e:
            print(f'第{cr}行,yn_itemid:{yn_itemid},上传出错 => e')
            self.ws.append([yn_itemid,'',pdes,yndes])


# import tkinter
# list_win=tkinter.Tk()
# list_win.wm_geometry("800x650")
# list_win.title('ximing')
# DataFra3(list_win).pack(fill=X,padx=3,pady=3)
# list_win.mainloop()